#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:robot
@file: tap_connector02_oracle.py 
@version:
@time: 2022/01/13 
"""
import cx_Oracle

from TapClientCaseData.ColumnGenerateData.dynamic.base.base02_oracle import *
from TapClientConnector.connector.tap_connector00_base import *


class TAPOracleConnector(TAPConnectorBase):

    def __init__(self, kwargs):
        super().__init__(kwargs)
        host = self.FORMAT_DB_INFO.pop('host')
        port = self.FORMAT_DB_INFO.pop('port')
        database = self.FORMAT_DB_INFO.pop('database')
        dsn = f'{host}:{port}/{database}'
        self.FORMAT_DB_INFO.update({'dsn': dsn})
        try:
            self.connector = cx_Oracle.connect(**self.FORMAT_DB_INFO)
        except Exception as e:
            logger.fatal(e)
            logger.fatal('INIT ORA CONNECTOR FAILED')

    @connector_query_logger
    def show_tables(self) -> list:
        table_list = []
        for i in self._query(f"select table_name from user_tables where USER='{self.connector_schema}'"):
            table_list.append(i[0])
        return table_list

    @connector_query_logger
    def show_dbs(self) -> list:
        db_list = []
        for i in self._query('SELECT * FROM dba_users'):
            db_list.append(i[0])
        return db_list

    # ***** Generate *****

    # ** DDL **

    @generate_logger
    def generate_create(self, table_column, table=None):
        if table:
            self.CURRENT_TABLE = table
        return f"""
                CREATE TABLE
                {self.connector_schema}."{self.CURRENT_TABLE}" {table_column}"""

    @generate_logger
    def generate_drop(self, table=None):
        if table:
            self.CURRENT_TABLE = table
        return f"""
                DROP TABLE 
                {self.connector_schema}."{self.CURRENT_TABLE}" """

    # **DML**

    @generate_logger
    def generate_insert(self, insert, table=None):
        """
        :param table:  table
        :param insert: [columns("column", "column",...), values] | [values]
        :return:
        """
        if table:
            self.CURRENT_TABLE = table
        if len(insert) == 1:
            return f"""
                    INSERT INTO 
                    {self.connector_schema}."{self.CURRENT_TABLE}" 
                    VALUES ({insert[0]})"""
        if len(insert) == 2:
            return f"""
                    INSERT INTO 
                    {self.connector_schema}."{self.CURRENT_TABLE}" ({insert[0]}) 
                    VALUES ({insert[1]})"""

    @generate_logger
    def generate_update(self, update: dict, where, table=None):
        if table:
            self.CURRENT_TABLE = table
        _update_list = [f'"{k}"={v}' for k, v in update.items()]
        COLUMNS = ",".join(_update_list)
        return f"""
                UPDATE
                {self.connector_schema}."{self.CURRENT_TABLE}" 
                SET {COLUMNS} {where}
                """

    @generate_logger
    def generate_delete(self, where, table=None):
        if table:
            self.CURRENT_TABLE = table
        return f"""
                DELETE FROM
                {self.connector_schema}."{self.CURRENT_TABLE}" {where}
                """

    @generate_logger
    def generate_select(self, column='*', where='', table=None, order=''):
        if table:
            self.CURRENT_TABLE = table
        return f"""
               SELECT {column} FROM 
               {self.connector_schema}."{self.CURRENT_TABLE}" {where} {order}
               """


if __name__ == '__main__':
    from TapClientCaseData.ColumnGenerateData.dynamic.dynamic_door import *

    t = TAPOracleConnector(Oracle.oracle_11g_USERQ)
    create = t.generate_create(t.salt, table_column=ORACLE_ALL_TYPE_COLUMNS)
    t.exec(create)
    drop = t.generate_drop()
    t.exec(drop)
    # t.generate_insert()
    # t.generate_update()
    # t.generate_delete()
    # t.generate_select()
